/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
import java.sql.*;
import java.util.Date;
import java.text.*;
import java.util.Calendar;

/**
 * 
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
public class Booking {
    private DB db;

    /**
     * Creates a new instance of the booking class, including the DB object.
     */
    public Booking() {
        this.db = new DB();
    }

    /**
     * Returns the MySQL result set object for the user and query specified.s
     * @param query
     * @param custID
     * @return ResultSet result
     */
    public ResultSet getDBResults(String query, String custID){
        //DB _db = new DB();
        Connection conn = db.connect();
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        return result;
    }

    /**
     * Adds booking entry to the database, returning true if successful and 
     * false if not.
     * @param custId
     * @param vehicleId
     * @param pDepot
     * @param rDepot
     * @param vehicleClass
     * @param bookingDate
     * @param startTime
     * @param endTime
     * @return
     */
    public int addBooking(int custId, int vehicleId, int pDepot, 
                int rDepot, int vehicleClass, String bookingDate, int startTime, int endTime) {
        //TODO:ad check in calling method, not here.
        if(startTime > endTime){
            System.out.println("Start time must occur before end time.");
            return -1;
        }
        Customer cust = new Customer();
        if(cust.getHoursLeft(custId) < (endTime - startTime) )
        {
            System.out.println("Not enough remaining hours");
            return -2;
        }
        int bookingStatus = -4; //db error return -4
        Connection conn = db.connect();
        String query = "INSERT INTO ScheduleDB (customerId,vehicleId,pickupDepotID,"
                +"returnDepotId,vehicleClass,bookingDate, bookingStartTime, bookingEndTime) "
                +"VALUES (" + custId + "," + vehicleId + "," + pDepot + "," + rDepot + "," 
                + vehicleClass + ",'" + bookingDate + "',"+startTime+","+endTime+")";
        try {
            db.putQuery(conn, query); 
            bookingStatus = 1;
        }
        catch (Exception e) { }
        db.disconnect(conn);
        return bookingStatus;
    }

    /**
     * Submits an SQL query to grab all bookings for the specified customer.
     * Returns a resulset object containing the booking entries.
     * @param customerID
     * @return ResultSet result
     */
    public ResultSet getCustomerBookings(int customerID){
        String query = "SELECT * FROM ScheduleDB WHERE customerId = "+customerID+" ORDER BY bookingId";
        ResultSet result = getDBResults(query,Integer.toString(customerID));
        return result;
    }

    /**
     * Returns true if the specified vehicle is booked for the given time.
     * @param vehicleId
     * @param bookingDate
     * @param startTime
     * @param endTime
     * @return boolean
     */
    public boolean isVehicleBooked(int vehicleId, String bookingDate, int startTime, int endTime) {
        Boolean status = true;
        Connection conn = db.connect();
        String query = "SELECT count(*) FROM ScheduleDB WHERE vehicleId="
                    +vehicleId+" AND bookingDate='"+bookingDate+"'"
                    +" AND ((bookingStartTime <="+startTime
                    +" AND bookingEndTime >="+(startTime-1)
                    +") OR (bookingStartTime <="+endTime
                    +" AND bookingEndTime>="+endTime+"))";
        ResultSet result = db.getQuery(conn, query);
        try {
            result.next();
            int count = result.getInt("count(*)");
            if (count==0) {
                status = false;
            }
        } catch (Exception e) {
            System.out.println( e.getMessage());
        } 
        return status;
    }

    /**
     * Created for the home screen. Returns a String array containing the 
     * booking info for the next booking scheduled, based on today's date.
     * @param customerID
     * @return info for the next booking
     */
    public String[] getNextBookingInfo(int customerID){
        String[] info = new String[9];
        for(int i = 0; i<9; i++){
            info[i] = "";
        }
        Date today = new Date();
        String _date = (today.getYear()+1900)+"-"+(today.getMonth()+1)+"-"+today.getDate();
        String _other = _date.replace("-", "");
        int _localDate = Integer.parseInt(_other);
        System.out.println("Today's Date: "+_localDate);
        String query = "SELECT * FROM ScheduleDB WHERE customerID = " + customerID;
        ResultSet result = getDBResults(query,Integer.toString(customerID));
        try{
            while(result.next()){
                info[0] = result.getInt("bookingId")+"";
                info[1] = result.getInt("customerID")+"";
                info[2] = result.getInt("vehicleID")+"";
                info[3] = result.getInt("pickupDepotID")+"";
                info[4] = result.getInt("returnDepotID")+"";
                info[5] = result.getInt("vehicleClass")+"";
                info[6] = result.getString("bookingDate")+"";
                info[7] = result.getInt("bookingStartTime")+"";
                info[8] = result.getInt("bookingEndTime")+"";
                String _tempDate = info[6].replace("-","");

                int _tempIntDate = Integer.parseInt(_tempDate);
                if(_tempIntDate >= _localDate) break;
            }
        }catch(Exception e){ }
        return info;
    }

    /**
     * Returns a 2D String array of all bookings in the specified date range,
     * for the specified customer.
     * @param customerID
     * @param _startDate
     * @param _endDate
     * @return the bookings, if they exist.
     */
    public String[][] getCustomerBookingsInRange(int customerID, int _startDate, int _endDate){
        String[][] info = new String[10][9];
        if((_endDate==0)||(_endDate < _startDate)){
            _startDate = 0;
            _endDate = 99999999;
        }
        Connection _conn = db.connect();
        String query = "SELECT * FROM ScheduleDB WHERE customerID = " + customerID;
        ResultSet result = db.getQuery(_conn, query);
        db.disconnect(_conn);
        //ResultSet result = getDBResults(query,Integer.toString(customerID));
        int i = 0;
        int flag = 0;
        try{
            while(result.next()){
                int _bookingDate = 0;
                try{
                    _bookingDate = Integer.parseInt((result.getString("bookingDate")+"").replace("-",""));
                } catch(Exception e){
                    System.out.println(e);
                    return null;
                }
                if((_bookingDate>=_startDate)&&(_bookingDate<=_endDate)){
                    info[i][0] = result.getInt("bookingId")+"";
                    info[i][1] = result.getInt("customerID")+"";
                    info[i][2] = result.getInt("vehicleID")+"";
                    info[i][3] = result.getInt("pickupDepotID")+"";
                    info[i][4] = result.getInt("returnDepotID")+"";
                    info[i][5] = result.getInt("vehicleClass")+"";
                    info[i][6] = result.getString("bookingDate")+"";
                    info[i][7] = result.getInt("bookingStartTime")+"";
                    info[i][8] = result.getInt("bookingEndTime")+"";
                    System.out.println("Date of Booking: "+info[i][6]);
                    flag = 1;
                }
                i++;
            }
        }catch(Exception e){ }
        return info;
    }

    /**
     * 
     * @param bookID
     * @return
     */
    public int checkCancelRights(int bookID){
        String[] currentDate;
        String[] date;
        String temp = " ";
        int startTime = 0;
        Connection conn = db.connect();
        String query = "SELECT * FROM ScheduleDB WHERE bookingId = "+bookID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            temp = result.getString("bookingDate");
            startTime = result.getInt("bookingStartTime");
        }catch(Exception e){}
        date = temp.split("-");
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd-HH");
        Calendar cal = Calendar.getInstance();
        currentDate = dateFormat.format(cal.getTime()).split("-");
        System.out.println("year = "+date[0]+" "+currentDate[0]+" Month = "+date[1]+" "+currentDate[1]);

        if(date[0].equals(currentDate[0]) && date[1].equals(currentDate[1])){
            int hoursTillBooking = (Integer.parseInt(date[2]) - Integer.parseInt(currentDate[2]) * 24) + (startTime - Integer.parseInt(currentDate[3]));
            if(hoursTillBooking < 24){
                return 1;
            }
            else if(hoursTillBooking < 12){
                return 2;
            }
            else
                return 3;
        }
        else{
            return 0;
        }
    }

    /**
     * Removes the specified booking from the database table, considering the 
     * cancellation policy and time frame.
     * @param bookID
     */
    public ResultSet getBooking(int bookID){
        Connection conn = db.connect();
        String query = "SELECT * FROM ScheduleDB WHERE bookingId = '" + bookID + "'";
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        return result; 
        
    }
    public void removeBooking(int bookID){
        int _result = checkCancelRights(bookID);
        int _custId = -1;
        int _oldHours = -1;
        int _s = -1;
        int _e = -1;
        int _hoursToAdd = 0;
        Customer _cust = new Customer();
        System.out.println("Result: "+_result);
        ResultSet _booking = getBooking(bookID);
        try{
           _booking.next();
          // getting the number of hours in the booking
           _s = _booking.getInt("bookingStartTime");
           _e = _booking.getInt("bookingEndTime");
           _custId = _booking.getInt("customerId");
           _oldHours = _cust.getHoursLeft(_custId);
           System.out.println("Old Hours: "+_oldHours);
        } catch(Exception e){
           System.out.println("No Booking with ID "+bookID+"!");
        }
                
        switch (_result){
            case 0: // normal, no-fee cancellation.
                _hoursToAdd = _e - _s;
                System.out.println("New Hours: "+_oldHours+_hoursToAdd);
                _cust.updateHours(_custId, _hoursToAdd);
                break;
            case 1://under 24 hours, 50% penalty
                _hoursToAdd = (int)Math.ceil((_e - _s)/2.0);
                System.out.println("hours to add = "+_hoursToAdd +"start and end = "+ _s+"and " +_e);
                _cust.updateHours(_custId, _hoursToAdd);
                break;
            case 2://under 12 hours, 75% penalty
                _hoursToAdd = (int)Math.ceil((_e - _s)*0.25);
                _cust.updateHours(_custId, _hoursToAdd);
                break;
            case 3://under 2 hours, 100% penalty
        }
        Connection conn = db.connect();
        String query = "DELETE FROM ScheduleDB WHERE bookingId = " + bookID;
        db.putQuery(conn, query);
        try{
            Thread.sleep(1000);
        }catch (InterruptedException e){
            System.out.println("Interrupted.");
        }
        db.disconnect(conn);
    }
    public ResultSet getDepotBookings(int depotID ){
        
        String query = "SELECT * FROM ScheduleDB WHERE pickupDepotId = "+depotID+" ORDER BY bookingStartTime";
        ResultSet result = getDBResults(query,Integer.toString(depotID));
        return result;
    }
}